use serde::Serialize;
use sqlx::{Executor, SqliteConnection};
use sqlx::Row;
use crate::{
    error::{new_err_static, AppErr, SqlxErr},
    utils::current_timestamp,
};

use super::get_pool;

const SQL_CREATE: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_dev_file (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        name TEXT NOT NULL, 
        create_timestamp INTEGER NOT NULL, 
        size INTEGER NOT NULL, 
        type_id INTEGER NOT NULL, 
        version TEXT NOT NULL, 
        description TEXT NOT NULL, 
        body BLOB NOT NULL,
        UNIQUE(type_id, version)
    )
"#;

pub async fn init() -> Result<(), SqlxErr> {
    get_pool().execute(SQL_CREATE).await?;
    Ok(())
}

async fn is_exists(
    tx: &mut SqliteConnection,
    type_id: i64,
    version: &str,
) -> Result<bool, SqlxErr> {
    let ret = sqlx::query(
        r#""
        SELECT id FROM tb_dev_file WHERE type_id = ? AND version = ?
    "#,
    )
    .bind(type_id)
    .bind(version)
    .fetch_optional(tx)
    .await?;
    Ok(ret.is_some())
}

pub async fn create(
    name: &str,
    type_id: i64,
    version: &str,
    description: &str,
    body: &[u8],
) -> Result<i64, AppErr> {
    let mut tx = get_pool().begin().await?;

    if is_exists(&mut tx, type_id, version).await? {
        return Err(new_err_static("固件已存在"));
    }

    sqlx::query(r#"
        INSERT INTO tb_dev_file(name, create_timestamp, size, type_id, version, description, body) VALUES (?,?,?,?,?,?,?)
    "#)
    .bind(name)
    .bind( current_timestamp()  as i64 )
    .bind( body.len() as i64 )
    .bind(type_id)
    .bind(version)
    .bind(description)
    .bind(body)
    .execute(&mut *tx)
    .await?;

    tx.commit().await?;

    todo!()
}

#[derive(Debug, Serialize)]
pub struct DevFile {
    id: i64,
    name: String,
    create_timestamp: i64,
    size: i64,
    version: String,
    description: String
}

pub async fn query(type_id: i64) -> Result<Box<[DevFile]>, SqlxErr> {

    let rows = sqlx::query(r#"
        SELECT id, name, create_timestamp, size, version, description WHERE type_id = ?
    "#)
    .bind(type_id)
    .fetch_all(get_pool())
    .await?;
    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let df = DevFile {
            id: row.get(0),
            name: row.get(1),
            create_timestamp: row.get(2),
            size: row.get(3),
            version: row.get(4),
            description: row.get(5)
        };
        vec.push(df);
    }
    Ok(vec.into_boxed_slice())
}

pub async fn delete(id: i64) -> Result<(), SqlxErr> {

    sqlx::query(r#"
        DELETE FROM tb_dev_file WHERE id = ?
    "#)
    .bind(id)
    .execute( get_pool() )
    .await?;

    Ok(())
}

pub async fn get(id: i64) -> Result<(String, Vec<u8>), AppErr> {

    let row = sqlx::query(r#"
        SELECT name, body FROM tb_dev_file WHERE id = ?
    "#)
    .bind(id)
    .fetch_optional(get_pool())
    .await?
    .ok_or(new_err_static("没有找到该文件"))?;

    let name: String = row.get(0);
    let body: Vec<u8> = row.get(1);

    Ok((name, body))
}

